-- ============================================================
-- DataDrop L1 Starter Database
-- Lesson 1: What is a Database?
-- ============================================================
-- HOW TO USE IN MICROSOFT ACCESS:
-- 1. Open Access and create a new blank database (DataDrop_L1.accdb)
-- 2. Go to Database Tools > Visual Basic (or press Alt+F11)
-- 3. Go to Tools > References and ensure "Microsoft Office 16.0 Access Database Engine" is checked
-- 4. Close VBA editor. Go to Create > Query Design
-- 5. Close the "Show Table" dialog
-- 6. Switch to SQL View (Home > View > SQL View)
-- 7. Paste each CREATE TABLE statement, run it, clear, then paste INSERTs
-- ============================================================
-- Alternatively: Import this file via External Data > Text File
-- Or use the queries below as individual SQL statements in Access SQL View
-- ============================================================

-- TABLE: Songs
-- This is the main table students will explore in Lesson 1
-- Fields: SongID, Title, Artist, Genre, ReleaseYear, DurationMins, Streams, IsExplicit

CREATE TABLE Songs (
    SongID       INTEGER     NOT NULL,
    Title        TEXT(100)   NOT NULL,
    Artist       TEXT(80)    NOT NULL,
    Genre        TEXT(30)    NOT NULL,
    ReleaseYear  INTEGER     NOT NULL,
    DurationMins DOUBLE      NOT NULL,
    Streams      INTEGER     NOT NULL,
    IsExplicit   YESNO       NOT NULL,
    CONSTRAINT PK_Songs PRIMARY KEY (SongID)
);

-- ============================================================
-- SAMPLE DATA — 20 records for Lesson 1 exploration
-- ============================================================

INSERT INTO Songs VALUES (1,  'Neon Skyline',      'The Midnight',     'Synthpop',  2021, 4.2,  2340000, FALSE);
INSERT INTO Songs VALUES (2,  'Ocean Drive',        'Duke Dumont',      'Electronic',2014, 3.8,  5100000, FALSE);
INSERT INTO Songs VALUES (3,  'Bad Habits',         'Ed Sheeran',       'Pop',       2021, 3.7,  9800000, FALSE);
INSERT INTO Songs VALUES (4,  'Blinding Lights',    'The Weeknd',       'Pop',       2019, 3.4, 12000000, FALSE);
INSERT INTO Songs VALUES (5,  'Heat Waves',         'Glass Animals',    'Indie',     2020, 3.9,  8500000, FALSE);
INSERT INTO Songs VALUES (6,  'Industry Baby',      'Lil Nas X',        'Hip-Hop',   2021, 3.6,  7200000, TRUE);
INSERT INTO Songs VALUES (7,  'Levitating',         'Dua Lipa',         'Pop',       2020, 3.5,  6800000, FALSE);
INSERT INTO Songs VALUES (8,  'Stay',               'Kid Laroi',        'Pop',       2021, 2.5,  9100000, TRUE);
INSERT INTO Songs VALUES (9,  'Wellerman',          'Nathan Evans',     'Folk',      2021, 2.2,  3200000, FALSE);
INSERT INTO Songs VALUES (10, 'Good 4 U',           'Olivia Rodrigo',   'Pop-Rock',  2021, 2.9,  7600000, FALSE);
INSERT INTO Songs VALUES (11, 'Peaches',            'Justin Bieber',    'R&B',       2021, 3.2,  5500000, TRUE);
INSERT INTO Songs VALUES (12, 'Montero',            'Lil Nas X',        'Pop',       2021, 2.3,  8000000, TRUE);
INSERT INTO Songs VALUES (13, 'Solar Power',        'Lorde',            'Indie',     2021, 3.8,  2100000, FALSE);
INSERT INTO Songs VALUES (14, 'Shivers',            'Ed Sheeran',       'Pop',       2021, 3.3,  5900000, FALSE);
INSERT INTO Songs VALUES (15, 'Permission to Dance','BTS',              'K-Pop',     2021, 3.2,  6300000, FALSE);
INSERT INTO Songs VALUES (16, 'Leave the Door Open','Silk Sonic',       'R&B',       2021, 4.0,  4700000, FALSE);
INSERT INTO Songs VALUES (17, 'Easy On Me',         'Adele',            'Pop',       2021, 3.4, 10200000, FALSE);
INSERT INTO Songs VALUES (18, 'Watermelon Sugar',  'Harry Styles',     'Pop',       2019, 2.8,  7800000, FALSE);
INSERT INTO Songs VALUES (19, 'drivers license',   'Olivia Rodrigo',   'Pop',       2021, 4.1,  9400000, FALSE);
INSERT INTO Songs VALUES (20, 'Miracle',            'Calvin Harris',    'Electronic',2022, 3.6,  3100000, FALSE);

-- ============================================================
-- PRE-BUILT QUERIES — create these in Access Query Design
-- Students run these in Lesson 1 to see how queries work
-- ============================================================

-- QUERY 1: All Pop songs (show all fields)
-- Name this: "Q1 Pop Songs"
-- SELECT * FROM Songs WHERE Genre = "Pop";

-- QUERY 2: Songs with over 5 million streams (show Title, Artist, Streams)
-- Name this: "Q2 High Streams"
-- SELECT Title, Artist, Streams FROM Songs WHERE Streams > 5000000 ORDER BY Streams DESC;

-- QUERY 3: Songs released in 2021 (show Title, Artist, ReleaseYear)
-- Name this: "Q3 Released 2021"
-- SELECT Title, Artist, ReleaseYear FROM Songs WHERE ReleaseYear = 2021 ORDER BY Title;

-- ============================================================
-- TEACHER NOTES:
-- Lesson 1 is about EXPLORATION. Students should not be creating queries themselves yet.
-- Pre-build Q1, Q2, Q3 in the Access file before giving to students.
-- Students should:
--   1. Open the database and look at the Songs table in Datasheet View
--   2. Count the records (20), identify the fields (8)
--   3. Run Q1, Q2, Q3 and answer worksheet questions based on results
-- ============================================================
